Microsoft Excel has got all sorts of different functions and they've broken these functions up into
various categories.
Earlier in this course we're taking a look at more logic based or conditionally based functions if function
summative count if if error.
Now we're going to take a look at the look up and reference category and we're going to take a look
at three or actually four of the more popular.
Look up and reference functions and we're going to start out with perhaps the most popular look up and
reference function the V look up or vertical Look-Up.
Now before we jump in and take a look at the inner workings of the vertical look up or view look up
function.
Let's talk about the sample that we have here.
What are you going to use this for and why is it so important for you.
So open in front of you.
I've got the exercise bio that comes with this course and I'm currently on the look up function worksheet
.
Now we're actually going to be working with two worksheets this one and right next door the master employee
of West this is going to allow us to combine between both of these worksheets here.
So first here's the setup.
So on this view look up worksheet.
I've got a simple but incomplete lists.
I've got some employee IDs and I've got some samples of the vertical look I've already completed that
are getting the last names.
Now these last names are actually contained or where I'm getting the data from is on the master employee
list.
I give that one a Click Here I've got the complete list.
I've got a list here of employee IDs last names first names departments and so on.
This would be considered the master lists.
Now let's imagine some department has come to you and said hey I need to get all of these employees
information.
All I've got is their IDs.
Here you go.
They give you a list of IDs.
All these employees now it's up to you to go get the relevant information that they now need about those
employees.
Well you have done this manually.
You have a gravelled list of my IDs are some numeric value identifying some record and then jumping
over and kind of manually picking through the list.
Oh here's 12 ninety their name was Cooper.
I'm going to copy and paste that to the other worksheet.
That's a lot of work to go through very very manual process.
But this is the idea of the Wii.
Look up the vertical look of.
We'll take some I.D. from us some numeric value that we get it and say hey vertical look I've got this
idea it's 1 0 5 4.
I want you to jump into this list over here called Master employee list find vertically right to be
look up find the employee ID once 054 in the first column of that list.
It will travel vertically looking for it when you find it bring the first name back to me or bring the
department back to me or whatever it is that you're asking for.
This is the Viiv look up function.
It's a simple look up give it some value tell it where to find that value Once it finds it tell it what
you want in connection with that value.
Let's try this out.
I need to go into SLC three is on the vertical look up her view look up function worksheet.
Now I want to get the first name for employing never one 054.
So I'm going to go to my formulas to.
I'm going to look up and reference and find Vlookup all the way down at the bottom.
All right.
And here's the argument window for the V look function.
So it's got four things that it wants from us in order for it to complete this task of finding the first
name based on these IDs.
The first thing it needs is we'll hey I know you want first name eventually but how am I going to find
the correct first name for you.
While the look of value this is going to be the ID a 3 or 1 054 so cell or view look up go look for
this value.
It's inside of 8:3.
Now where am I going to look for it.
That's the next argument.
The table array.
Great you given me this idea.
When 054.
Well where do you want me to go look for it.
Well table array.
I'm going to go down to master employee list and I'm going to grab this entire list year starting in
a three then I'm going to do control shift down here on my keyboard.
This will grab that entire column and then I'm going to do control shift.
Right arrow I'm going to grab the entire lists from a 1 to i.
38.
So we got the value you're looking for.
We've now told them where to look for it.
And by default a vertical look up looks vertically through the first column of the table array.
The list that you just specified here.
Now before I go back to the other worksheet the next one.
Call index number.
Well I'm looking for this.
I'm going to find it here.
What do you want me to return back to you.
That's what the column index number is.
Well I need you to return first name which is in Column number 1 2 3.
So I'm going to say call them in this number is equal to 3.
So I'll find this for me.
Look forward over here and return the third column's value back to me.
Now the last one here at the last argument this is optional.
Although for me I'd say like 95 plus percent of the time I use it.
What this is.
And if you just click in one of these arguments you get a brief little description down below.
It's a logical value to find the closest match in the first column sort in or put in the word true or
leave it blank if you want to find the exact match and this really deals with what it's looking up.
There's a three value.
There's one 054 to find the exact match you're going to put the word false in there.
So for me you know I think that this is employee information.
If a kid find employee number one 054 over and that master list I don't want the closest employee to
that number.
I want the exact employee.
And if he can't find it then don't return anything back to me or give me the standard error.
So in this case I'm going to put the word false and that's it for little things.
What am I looking for.
Where am I going to find it.
What do you want it returned back to you.
And whether or not you want the exact match or not that's it.
I hit OK.
There's Howard Howard Smith is Employee Number one 054.
And if I just confirm this master employee lists there it is Howard Smith.
I'll go back then I'm going to finish it off.
I've got that little box in the corner.
Just drag this down.
And I got all the first names for these employees.
Try this out.
Open up the example file.
Complete the first name of the department do pay rates complete these look of values based on the master
employee list